package com.bt.lms.dao.servise.impl;

import com.bt.lms.entity.Role;
import com.bt.lms.entity.User;
import com.bt.lms.entity.page.Page;
import com.bt.lms.dao.entity.RlUserRolePo;
import com.bt.lms.dao.servise.UserDao;

import java.sql.*;
import java.time.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 用户服务dao实现
 *
 * @author 徐大永
 */
public class UserDaoImpl implements UserDao {

    @Override
    public Long insert(String sql, User user) {

        //入库的属性
        String accountNumber = user.getAccountNumber();
        String name = user.getName();
        Integer codeType = user.getCodeType();
        String code = user.getCode();
        String unit = user.getUnit();
        String mobilePhone = user.getMobilePhone();
        String createdName = user.getCreatedName();
        LocalDateTime createdTime = user.getCreatedTime();
        String createdTimeStr = createdTime.toString();
        Integer status = user.getStatus();
        String password = user.getPassword();
        Integer deleted = user.getDeleted();
        try {
            //连接
            Connection connection = pond();
            //域处理对象
            PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            preparedStatement.setString(1, accountNumber);
            preparedStatement.setString(2, name);
            preparedStatement.setInt(3, codeType);
            preparedStatement.setString(4, code);
            preparedStatement.setString(5, unit);
            preparedStatement.setString(6, mobilePhone);
            preparedStatement.setString(7, createdName);
            preparedStatement.setString(8, createdTimeStr);
            preparedStatement.setInt(9, status);
            preparedStatement.setString(10, password);
            preparedStatement.setInt(11, deleted);
            //执行SQL
            preparedStatement.executeUpdate();
            //preparedStatement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
            //获取id
            ResultSet userKeys = preparedStatement.getGeneratedKeys();
            userKeys.next();
            long userId = userKeys.getLong(1);
            //关闭资源
            preparedStatement.close();
            connection.close();
            return userId;
        } catch (SQLException e) {
            throw new RuntimeException(e);//获取连接
        }

    }

    @Override
    public int insertRlS(String sql, List<Long> userIds, Long roleId) {

        try {
            //连接
            Connection connection = pond();
            //域处理对象
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (long userId : userIds) {
                String installRoleTime = LocalDateTime.now().toString();
                preparedStatement.setLong(1, userId);
                preparedStatement.setLong(2, roleId);
                preparedStatement.setString(3, installRoleTime);
                preparedStatement.addBatch();
            }
            //执行SQL
            int[] ints = preparedStatement.executeBatch();
            int con = ints.length;
            //关闭资源
            preparedStatement.close();
            connection.close();
            return con;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public User selectUser(String sql, Long id) {
        //要返回的对象
        User user = new User();
        user.setId(id);

        //连接
        Connection connection = pond();
        try {
            //域加载
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1, id);
            //SQL执行
            ResultSet resultSet = preparedStatement.executeQuery();
            boolean next = resultSet.next();
            if (next) {
                //找到对应资源
                String accountNumber = resultSet.getString("account_number");
                String name = resultSet.getString("name");
                int codeType = resultSet.getInt("code_type");
                String code = resultSet.getString("code");
                String unit = resultSet.getString("unit");
                String mobilePhone = resultSet.getString("mobile_phone");
                String createdName = resultSet.getString("created_name");
                Timestamp createdTimestamp = resultSet.getTimestamp("created_time");
                Timestamp loginTimestamp = resultSet.getTimestamp("login_time");
                int status = resultSet.getInt("status");
                //带走
                user.setAccountNumber(accountNumber);
                user.setName(name);
                user.setCodeType(codeType);
                user.setCode(code);
                user.setUnit(unit);
                user.setMobilePhone(mobilePhone);
                user.setCreatedName(createdName);
                //创建时间
                long createdTime = createdTimestamp.getTime();
                LocalDateTime createdLDT = LocalDateTime.ofInstant(Instant.ofEpochMilli(createdTime)
                        , ZoneOffset.ofHours(0));
                user.setCreatedTime(createdLDT);
                //登录时间
                if (loginTimestamp == null) {
                    user.setLogInTime(null);
                } else {
                    long loginTime = loginTimestamp.getTime();
                    LocalDateTime loginLDT = LocalDateTime.ofInstant(Instant.ofEpochMilli(loginTime)
                            , ZoneOffset.ofHours(0));
                    user.setLogInTime(loginLDT);
                }
                user.setStatus(status);
                user.setPassword("123456");

                return user;
            } else {//未找到
                //5.关闭资源
                preparedStatement.close();
                connection.close();
                return null;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public List<Role> selectRole(String sqlRl, User user) {

        //连接
        Connection connection = pond();
        //域加载
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sqlRl);
            preparedStatement.setLong(1, user.getId());

            //执行
            ResultSet resultSet = preparedStatement.executeQuery();

            //需要的对象
            List<Role> roles = new ArrayList<>();
            user.setRoles(roles);
            //处理结果集
            while (resultSet.next()) {
                String roleName = resultSet.getString("name");
                Role role = new Role();
                role.setName(roleName);
                roles.add(role);
            }

            //关闭资源
            preparedStatement.close();
            connection.close();

            return roles;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<RlUserRolePo> selectRl(String sqlRl, Long id) {
        //要返回的对象
        List<RlUserRolePo> rlUserRolePos = new ArrayList<>();
        try {
            //连接
            Connection conRl = pond();
            //域加载
            PreparedStatement staRL = conRl.prepareStatement(sqlRl);
            staRL.setLong(1, id);
            //SQL执行
            ResultSet rlRS = staRL.executeQuery();
            while (rlRS.next()) {
                //单个关系
                RlUserRolePo rlUserRolePo = new RlUserRolePo();
                //long userId = rlRS.getLong("user_id_");//用户id
                long roleId = rlRS.getLong("role_id");//角色id
                Timestamp installRoleTimestamp = rlRS.getTimestamp("install_role_time");//关联时间
                //带走
                if (installRoleTimestamp != null) {
                    long installRoleTime = installRoleTimestamp.getTime();
                    LocalDateTime installTime = LocalDateTime.ofInstant(Instant.ofEpochMilli(installRoleTime), ZoneOffset.ofHours(0));
                    rlUserRolePo.setInstallRoleTime(installTime);
                }
                //rlUserRolePo.setUserId(userId);
                rlUserRolePo.setRoleId(roleId);
                //关系集
                rlUserRolePos.add(rlUserRolePo);
            }
            //关闭资源
            staRL.close();
            conRl.close();
            return rlUserRolePos;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public Role select(String roleSql, Long id) {

        //要返回的对象
        Role role = new Role();
        //连接
        Connection conRole = pond();
        //域加载
        try {
            PreparedStatement preparedStatement = conRole.prepareStatement(roleSql);
            preparedStatement.setLong(1, id);
            //SQL执行
            ResultSet roleRS = preparedStatement.executeQuery();
            roleRS.next();
            String code = roleRS.getString("code");
            String name = roleRS.getString("name");
            //带走
            role.setCode(code);
            role.setName(name);
            //资源关闭
            preparedStatement.close();
            conRole.close();
            return role;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<User> select(String sql, Page<User> userPage, Long roleId) {

        //要返回的对象
        List<User> users = new ArrayList<>();

        //需要的数据
        Integer size = userPage.getSize();
        Integer current = userPage.getCurrent();
        int startIndex = size * (current - 1);

        try {
            //连接（查询中间表以及用户全表）
            Connection connection = pond();
            //预编译
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1, roleId);
            preparedStatement.setLong(2, startIndex);
            preparedStatement.setLong(3, size);
            //执行
            ResultSet resultSet = preparedStatement.executeQuery();

            //处理结果集
            while (resultSet.next()) {

                //查询数据
                long id = resultSet.getLong("id");
                String accountNumber = resultSet.getString("account_number");
                String name = resultSet.getString("name");
                String createdName = resultSet.getString("created_name");
                Timestamp createdTimeTS = resultSet.getTimestamp("created_time");
                Timestamp loginTimeTS = resultSet.getTimestamp("login_time");
                int status = resultSet.getInt("status");

                //带走
                User user = new User();
                user.setId(id);
                user.setAccountNumber(accountNumber);
                user.setName(name);
                user.setCreatedName(createdName);
                //创建时间
                long createdTime = createdTimeTS.getTime();
                LocalDateTime createdTimeLDT = LocalDateTime.ofInstant(Instant.ofEpochMilli(createdTime), ZoneOffset.ofHours(0));
                user.setCreatedTime(createdTimeLDT);
                //最近登录时间
                if (loginTimeTS != null) {
                    long loginTime = loginTimeTS.getTime();
                    LocalDateTime loginTimeLDT = LocalDateTime.ofInstant(Instant.ofEpochMilli(loginTime), ZoneOffset.ofHours(0));
                    user.setLogInTime(loginTimeLDT);
                } else {
                    user.setLogInTime(null);
                }
                user.setStatus(status);
                users.add(user);
            }

            //关闭资源
            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return users;
    }

    @Override
    public Long selectSum(String roleSum, Long roleId) {


        try {
            //连接
            Connection connection = pond();
            //预编译
            PreparedStatement preparedStatement = connection.prepareStatement(roleSum);
            preparedStatement.setLong(1, roleId);
            //执行
            ResultSet resultSet = preparedStatement.executeQuery();

            //处理结果集
            resultSet.next();
            long sum = resultSet.getLong(1);

            //关闭资源
            preparedStatement.close();
            connection.close();

            return sum;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public Long selectSum(String userSum) {
        try {
            //连接
            Connection connection = pond();
            //域连接
            PreparedStatement preparedStatement = connection.prepareStatement(userSum);
            //执行
            ResultSet resultSet = preparedStatement.executeQuery();
            resultSet.next();
            return resultSet.getLong(1);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public boolean deleteRl(String rlSql, Long id) {

        try {
            //连接
            Connection connection = pond();
            //域连接
            PreparedStatement preparedStatement = connection.prepareStatement(rlSql);
            preparedStatement.setLong(1, id);
            //执行
            preparedStatement.executeUpdate();
            return true;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public boolean delete(String userSql, Long id) {

        try {
            //连接
            Connection connection = pond();
            //域编译
            PreparedStatement preparedStatement = connection.prepareStatement(userSql);
            preparedStatement.setLong(1, id);
            //执行
            int i = preparedStatement.executeUpdate();
            return i == 1;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int update(String sql, Long id, Object i) {

        try {
            //连接
            Connection connection = pond();
            //域加载
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, i);
            preparedStatement.setObject(2, id);
            //执行
            int i1 = preparedStatement.executeUpdate();
            //关闭
            preparedStatement.close();
            connection.close();

            return i1;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public int update(String userSql, User user) {

        //要修改的属性 UPDATE user SET account_number=?,name=?,code_type=?,code=?,unit=?,mobile_phone=? WHERE id = ?
        String accountNumber = user.getAccountNumber();
        String name = user.getName();
        Integer codeType = user.getCodeType();
        String code = user.getCode();
        String unit = user.getUnit();
        String mobilePhone = user.getMobilePhone();
        Long id = user.getId();

        try {
            //获取连接
            Connection connection = pond();
            //域处理
            PreparedStatement preparedStatement = connection.prepareStatement(userSql);
            preparedStatement.setString(1, accountNumber);
            preparedStatement.setString(2, name);
            preparedStatement.setInt(3, codeType);
            preparedStatement.setString(4, code);
            preparedStatement.setString(5, unit);
            preparedStatement.setString(6, mobilePhone);
            preparedStatement.setLong(7, id);
            //执行
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void query(String userSql, Page page, List<User> users, String userSumSql) {

        //开始位置
        Integer size = page.getSize();//每页数量
        Integer current = page.getCurrent();//页码
        int front = size * (current - 1);

        try {
            //连接
            Connection connection = pond();
            //域执行对象
            PreparedStatement preparedStatement = connection.prepareStatement(userSql);
            preparedStatement.setInt(1, front);
            preparedStatement.setInt(2, size);

            //执行（用户总数）
            ResultSet resultSet = preparedStatement.executeQuery(userSumSql);
            resultSet.next();
            long aLong = resultSet.getLong(1);
            page.setTotal(aLong);

            //执行 (具体用户)
            ResultSet userRS = preparedStatement.executeQuery();
            while (userRS.next()) {

                User user = new User();

                long id = userRS.getLong("id");
                String accountNumber = userRS.getString("account_number");
                String name = userRS.getString("name");
                String createdName = userRS.getString("created_name");
                Timestamp createdTimestamp = userRS.getTimestamp("created_time");
                Timestamp loginTimestamp = userRS.getTimestamp("login_time");
                int status = userRS.getInt("status");

                //带走
                user.setId(id);
                user.setAccountNumber(accountNumber);
                user.setName(name);
                user.setCreatedName(createdName);
                //创建时间
                long createdTime = createdTimestamp.getTime();
                LocalDateTime createdLDT = LocalDateTime.ofInstant(Instant.ofEpochMilli(createdTime)
                        , ZoneOffset.ofHours(0));
                user.setCreatedTime(createdLDT);
                //登录时间
                if (loginTimestamp == null) {
                    user.setLogInTime(null);
                } else {
                    long loginTime = loginTimestamp.getTime();
                    LocalDateTime loginLDT = LocalDateTime.ofInstant(Instant.ofEpochMilli(loginTime)
                            , ZoneOffset.ofHours(0));
                    user.setLogInTime(loginLDT);
                }
                user.setStatus(status);
                users.add(user);
            }

            //关闭资源
            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public List<Long> queryRl(String rlSql, Long userId) {
        //要返回的对象
        List<Long> roleIds = new ArrayList<>();
        try {
            //连接
            Connection connection = pond();
            //域处理
            PreparedStatement preparedStatement = connection.prepareStatement(rlSql);
            preparedStatement.setLong(1, userId);
            //执行
            ResultSet resultSet = preparedStatement.executeQuery();
            //处理结果集
            while (resultSet.next()) {
                long roleId = resultSet.getLong("role_id");
                roleIds.add(roleId);
            }
            //关闭资源
            preparedStatement.close();
            connection.close();

            return roleIds;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Role queryRole(String roleSql, Long roleId) {
        //要返回的对象
        Role role = new Role();
        try {
            //连接
            Connection connection = pond();
            //域处理
            PreparedStatement preparedStatement = connection.prepareStatement(roleSql);
            preparedStatement.setLong(1, roleId);
            //执行
            ResultSet resultSet = preparedStatement.executeQuery();
            //处理结果集
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                role.setName(name);
            }
            //关闭资源
            preparedStatement.close();
            connection.close();

            return role;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    /**
     * 连接
     * （后续替换为连接池）
     *
     * @return 连接
     */
    public static Connection pond() {
        //1.加载驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接
            String url = "jdbc:mysql://rm-cn-zxu39exkm001edxo.rwlb.rds.aliyuncs.com:3306/bot_lms?useSSL=false";
            String userStr = "root";
            String pwd = "Bot-417-1";
            return DriverManager.getConnection(url, userStr, pwd);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);//驱动
        } catch (SQLException e) {
            throw new RuntimeException(e);//连接
        }

    }

}
